import Database from 'better-sqlite3';
import { Config } from './config';

let db: Database.Database;

const SQL_TABLE_EXIST = `select name from sqlite_master where type = 'table' and name = ?;`;

const SQL_CREATE_TB_BOOK = `
CREATE TABLE tb_book (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  book_name TEXT NOT NULL,
  book_author TEXT,
  book_desc TEXT,
  book_label TEXT,
  sha256 TEXT NOT NULL,
  state TEXT NOT NULL DEFAULT 'enable',
  create_time INTEGER NOT NULL DEFAULT (
    CAST (
      (julianday('now') - 2440587.5) * 86400000 AS INTEGER
    )
  ),
  score numeric (2),
  download_times INTEGER NOT NULL DEFAULT 0,
  file_size INTEGER DEFAULT 0
)`;

const SQL_CREATE_TB_CHAPTER = `
CREATE TABLE tb_chapter (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	book_id INTEGER NOT NULL,
	chapter TEXT NOT NULL,
	gzip_content BLOB NOT NULL,
	CONSTRAINT tb_chapter_tb_book_FK FOREIGN KEY (book_id) REFERENCES tb_book(id) ON DELETE CASCADE ON UPDATE CASCADE
)`;

type TableDef = {
	name: string;
};

function initDB() {
	let table = db.prepare<unknown[], TableDef>(SQL_TABLE_EXIST).get('tb_book');
	if (!table) {
		db.exec(SQL_CREATE_TB_BOOK);
	}
	table = db.prepare<unknown[], TableDef>(SQL_TABLE_EXIST).get('tb_chapter');
	if (!table) {
		db.exec(SQL_CREATE_TB_CHAPTER);
	}
}

export function openDatabase(file?: string) {
	if (db) {
		if (db.open) {
			return db;
		} else {
			closeDatabase();
		}
	}
	db = new Database(file ?? Config.dbFile);
	initDB();
	return db;
}

export function closeDatabase() {
	if (db && db.open) {
		db.close();
	}
}
